<?php
include_once 'template/header_index.php';
// 参考：http://www.php.net/manual/zh/pdo.connections.php
// https://segmentfault.com/a/1190000004106175

include_once 'data/db_config.php';
try {
    $dbh = new PDO('mysql:host=localhost;dbname=' . $db['db_name'], $db['user'], $db['pwd'], array(PDO::ATTR_PERSISTENT => true));
    $dbh->query('set names utf8');

    // 获取昨日的总户总量
    $sql_user = 'SELECT cumulate_user FROM `usercumulate` order by ref_date desc limit 1';
    $res = $dbh->query($sql_user);
    $total_user = $res->fetchColumn();

    // 获取总文章数量
    $sql_article_count = 'SELECT title FROM `wx`.`articlesummary` GROUP BY title;';
    $res1 = $dbh->query($sql_article_count);
    $total_article = count($res1->fetchAll());

    //  获取过去7天的用户数据
    $last_7_day = date("Y-m-d", strtotime('-7 days'));
    $end_day = date('Y-m-d', strtotime('-1 days'));
    $sql_users = 'SELECT * FROM `user` where ref_date>="' . $last_7_day . '" and ref_date<="' . $end_day . '"';

    //  获取过去14天的图文数据
    $last_14_day = date("Y-m-d", strtotime('-14 days'));
    $sql_article = 'SELECT ref_date,msgid,title,details FROM `articletotal` where ref_date>="' . $last_14_day . '" and ref_date<= "' . $end_day . '" group by msgid';

    //  获取stat表的最新一次统计的日期
    $sql_stat_date = 'SELECT date FROM `stat` order by date desc limit 1';
    $res4 = $dbh->query($sql_stat_date);
    $stat_date = $res4->fetchColumn();
    
    $last13day = date("Y-m-d", strtotime($stat_date)-3600*24*13);
    $last6day = date("Y-m-d", strtotime($stat_date)-3600*24*6);
    $last7day = date("Y-m-d", strtotime($stat_date)-3600*24*7);
//    $last1day = date('Y-m-d', strtotime($stat_date)-3600*24);
    
//    $last7day = date("Y-m-d", strtotime('last Monday'));
//    $last1day = date("Y-m-d", strtotime('last Sunday'));
    
    $last2_stat = 'SELECT * FROM `stat` where date>="' . $last13day . '" and date<= "' . $last7day . '"';
    $last1_stat = 'SELECT * FROM `stat` where date>="' . $last6day . '" and date<= "' . $stat_date . '"';


    // 获取博客最新一次统计的日期
    $sql_blog_date = 'SELECT stat_date FROM `blog` order by stat_date desc limit 1';
    $res2 = $dbh->query($sql_blog_date);
    $blog_date = $res2->fetchColumn();
    $sql_blog = 'SELECT * FROM `blog` where stat_date="' . $blog_date . '"';

    // 获取微信群最新一次统计的日期
    $sql_wx_date = 'SELECT stat_date FROM `wx_group` order by stat_date desc limit 1';
    $res3 = $dbh->query($sql_wx_date);
    $wx_date = $res3->fetchColumn();
    $sql_wx_group = 'SELECT * FROM `wx_group` where stat_date="' . $wx_date . '"';
    ?>
    <div class="col-sm-9 col-sm-offset-3 col-md-10 col-md-offset-2 main">
        <h1><strong id="chart_title"><?= $name ?></strong></h1>
        <hr>
        <h2 class="sub-header">近2周<span style="color: #c00;">基本数据</span></h2>
        <div class="row placeholders">
            <!--            <div class="col-xs-6 col-sm-2 placeholder" style="height: 180px; background-color:#0b8d9a;border-radius: 50%;color: #fff;font-weight: bold;font-size: 18px;">
                            <br><br><br>
                            <span><?= $total_user ?></span>
                            <br>
                            <p>总粉丝数</p>
                        </div>-->
            <div class="table-responsive col-xs-6 col-sm-10 placeholder">
                <table class="table table-bordered  table-hover table-condensed">
                    <thead><tr style='font-weight:bold;'><td>日期区间</td><td>PV</td><td>UV</td><td>消费</td><td>点击量</td><td>离线宝   电话沟通</td><td>商桥留言</td><td>商桥咨询人数</td><td>搜索引擎线索</td><td>总销售线索</td></tr></thead>
                    <tbody>
                        <?php
                        $pv = 0;
                        $uv = 0;
                        $cost = 0;
                        $click = 0;
                        $tel = 0;
                        $message = 0;
                        $consult = 0;
                        $se_key = 0;
                        $total_key = 0;
                        foreach ($dbh->query($last2_stat) as $row) {
                            $pv += $row['pv'];
                            $uv += $row['uv'];
                            $cost += $row['cost'];
                            $click += $row['click'];
                            $tel += $row['tel'];
                            $message += $row['message'];
                            $consult += $row['consult'];
                            $se_key += $row['se_key'];
                            $total_key += $row['total_key'];
                        }
//                        echo $cost . '-';
                        $pv2 = 0;
                        $uv2 = 0;
                        $cost2 = 0;
                        $click2 = 0;
                        $tel2 = 0;
                        $message2 = 0;
                        $consult2 = 0;
                        $se_key2 = 0;
                        $total_key2 = 0;
                        foreach ($dbh->query($last1_stat) as $res) {
                            $pv2 += $res['pv'];
                            $uv2 += $res['uv'];
                            $cost2 += $res['cost'];
                            $click2 += $res['click'];
                            $tel2 += $res['tel'];
                            $message2 += $res['message'];
                            $consult2 += $res['consult'];							
                            $se_key2 += $res['se_key'];
                            $total_key2 += $res['total_key'];
                        }
//                        echo $cost2;
//                        $stat = array();
                        $stat['pv'] = round(($pv2 - $pv)/$pv*100,2);
                        $stat['uv'] = round(($uv2 - $uv)/$uv*100, 2);
                        $stat['cost'] = round(($cost2 - $cost)/$cost*100,2);
                        $stat['click'] = round(($click2 - $click)/$click*100,2);
                        if($tel != 0){
                            $stat['tel'] = round(($tel2 - $tel)/$tel*100,2);
                        }  else {
                            $stat['tel'] = '+';
                        }
                        $stat['message'] = round(($message2 - $message)/$message*100,2);
                        $stat['consult'] = round(($consult2 - $consult)/$consult*100,2);
                        $stat['se_key'] = round(($se_key2 - $se_key)/$se_key*100,2);
                        $stat['total_key'] = round(($total_key2 - $total_key)/$total_key*100,2);
//                        var_dump($stat);
                        echo '<tr>';
                        echo '<td>' . $last13day . ' - ' . $last7day . '</td>';
                        echo '<td>' . $pv . '</td>';
                        echo '<td>' . $uv . '</td>';
                        echo '<td>' . $cost . '</td>';
                        echo '<td>' . $click . '</td>';
                        echo '<td>' . $tel . '</td>';
                        echo '<td>' . $message . '</td>';
                        echo '<td>' . $consult . '</td>';
                        echo '<td>' . $se_key . '</td>';
                        echo '<td>' . $total_key . '</td>';
                        echo '</tr>';
                        echo '<tr>';
                        echo '<td>' . $last6day . ' - ' . $stat_date . '</td>';
                        echo '<td>' . $pv2 . '</td>';
                        echo '<td>' . $uv2 . '</td>';
                        echo '<td>' . $cost2 . '</td>';
                        echo '<td>' . $click2 . '</td>';
                        echo '<td>' . $tel2 . '</td>';
                        echo '<td>' . $message2 . '</td>';
                        echo '<td>' . $consult2 . '</td>';						
                        echo '<td>' . $se_key2 . '</td>';
                        echo '<td>' . $total_key2 . '</td>';
                        echo '</tr>';
                        echo '<tr style="font-weight:bold;color:#0B8D9A;">';
                        echo '<td>环比</td>';
                        echo '<td>' . $stat['pv'] . '%</td>';
                        echo '<td>' . $stat['uv'] . '%</td>';
                        echo '<td>' . $stat['cost'] . '%</td>';
                        echo '<td>' . $stat['click'] . '%</td>';
                        echo '<td>' . $stat['tel'] . '%</td>';
                        echo '<td>' . $stat['message'] . '%</td>';
                        echo '<td>' . $stat['consult'] . '%</td>';
                        echo '<td>' . $stat['se_key'] . '%</td>';
                        echo '<td>' . $stat['total_key'] . '%</td>';
                        echo '</tr>';
                        ?>
                    </tbody>
                </table>
            </div>            
        </div>        
        <h2 class="sub-header">近7天<span style="color: #c00;">用户数据</span></h2>
        <div class="row placeholders">
            <div class="col-xs-6 col-sm-2 placeholder" style="height: 180px; background-color:#0b8d9a;border-radius: 50%;color: #fff;font-weight: bold;font-size: 18px;">
                <br><br><br>
                <span><?= $total_user ?></span>
                <br>
                <p>总粉丝数</p>
            </div>
            <div class="table-responsive col-xs-6 col-sm-8 placeholder">
                <table class="table table-bordered  table-hover table-condensed">
                    <thead><tr style='font-weight:bold;'><td>日期</td><td>新增用户数</td><td>取消用户数</td><td>净增用户数</td><td>总用户数</td></tr></thead>
                    <tbody>
                        <?php
                        $new = 0;
                        $cancel = 0;
                        $net = 0;
                        $total = 0;
                        foreach ($dbh->query($sql_users) as $row) {
                            echo '<tr>';
                            echo '<td>' . $row['ref_date'] . '</td>';
                            echo '<td>' . $row['new_user'] . '</td>';
                            echo '<td>' . $row['cancel_user'] . '</td>';
                            if ($row['net_user'] >= 50) {
                                echo '<td style="background-color:#FFAF3E;">' . $row['net_user'] . '</td>';
                            } else {
                                echo '<td>' . $row['net_user'] . '</td>';
                            }
                            echo '<td>' . $row['cumulate_user'] . '</td>';
                            echo '</tr>';
                            $new += intval($row['new_user']);
                            $cancel += intval($row['cancel_user']);
                            $net += intval($row['net_user']);
                            $total = intval($row['cumulate_user']);
                        }
                        echo '<tr style="font-weight:bold;color:#0B8D9A;"><td>Total</td><td>' . $new . '</td><td>' . $cancel . '</td><td>' . $net . '</td><td>-</td></tr>';
                        ?>
                    </tbody>
                </table>
                <p><em>备注：</em>黄色单元格表示净增用户数大于50.</p>
            </div>            
        </div>
        <h2 class="sub-header">近14天<span style="color: #c00;">图文数据</span></h2>
        <div class="row placeholders">
            <div class="col-xs-6 col-sm-2 placeholder" style="height: 180px; background-color:#ffaf3e;border-radius: 50%;color: #fff;font-weight: bold;font-size: 18px;">
                <br><br><br>
                <span><?= $total_article ?></span>
                <p>总文章数</p>
            </div>
            <div class="table-responsive col-xs-6 col-sm-9 placeholder">
                <table class="table table-bordered  table-hover table-condensed">
                    <thead><tr style='font-weight:bold;'><td>日期</td><td>文章标题</td><td>送达人数</td><td>图文阅读人数</td><td>分享人数</td><td>收藏人数</td></tr></thead>
                    <tbody>
                        <?php
                        foreach ($dbh->query($sql_article) as $article) {
                            $arr['date'][] = $article['ref_date'];
                            $arr['title'][] = $article['title'];
//                            $arr['msgid'][] = $article['msgid'];
                            // 把json数据转化为数组，将第二个参数为true时将转化为数组
                            $details_arr = json_decode($article['details'], true);
                            $details_key = count($details_arr) - 1;

                            $arr['target_user'][] = $details_arr[$details_key]['target_user'];
                            $arr['int_page_read_user'][] = $details_arr[$details_key]['int_page_read_user'];
                            $arr['share_user'][] = $details_arr[$details_key]['share_user'];
                            $arr['add_to_fav_user'][] = $details_arr[$details_key]['add_to_fav_user'];

                            echo '<tr>';
                            echo '<td>' . $article['ref_date'] . '</td>';
                            echo '<td>' . $article['title'] . '</td>';
                            echo '<td>' . $details_arr[$details_key]['target_user'] . '</td>';
                            if ($details_arr[$details_key]['int_page_read_user'] >= 1000) {
                                echo '<td style="background-color:#FFAF3E;">' . $details_arr[$details_key]['int_page_read_user'] . '</td>';
                            } else {
                                echo '<td>' . $details_arr[$details_key]['int_page_read_user'] . '</td>';
                            }
                            echo '<td>' . $details_arr[$details_key]['share_user'] . '</td>';
                            echo '<td>' . $details_arr[$details_key]['add_to_fav_user'] . '</td>';
                            echo '</tr>';
                        }
                        ?>
                    </tbody>
                </table>
                <p><em>备注：</em>黄色单元格表示阅读数大于1000.</p>
            </div>            
        </div>
        <h2 class="sub-header"><?= $blog_date ?> <span style="color: #c00;">CSDN博客数据</span></h2>
        <div class="row placeholders">
            <!--            <div class="col-xs-6 col-sm-2 placeholder" style="height: 180px; background-color:#ffaf3e;border-radius: 50%;color: #fff;font-weight: bold;font-size: 18px;">
                            <br><br><br>
                            <span><?= $total_article ?></span>
                            <p>总阅读数</p>
                        </div>-->
            <div class="table-responsive col-xs-6 col-sm-9 placeholder">
                <p><em>备注：</em>黄色单元格表示阅读量大于5000.</p>
                <table class="table table-bordered  table-hover table-condensed">
                    <thead><tr style='font-weight:bold;'><td>作者</td><td>URL</td><td>访问量</td><td>积分</td><td>原创</td><td>转载</td><td>译文</td><td>评论</td></tr></thead>
                    <tbody>
                        <?php
                        $visit = 0;
                        $integration = 0;
                        $original = 0;
                        $reprint = 0;
                        $version = 0;
                        $comment = 0;
                        foreach ($dbh->query($sql_blog) as $blog) {
                            echo '<tr>';
                            echo '<td>' . $blog['author'] . '</td>';
                            echo '<td style="text-align:left;">' . $blog['url'] . '</td>';
                            if ($blog['visit'] >= 5000) {
                                echo '<td style="background-color:#FFAF3E;">' . $blog['visit'] . '</td>';
                            } else {
                                echo '<td>' . $blog['visit'] . '</td>';
                            }
                            echo '<td>' . $blog['integration'] . '</td>';
                            echo '<td>' . $blog['original'] . '</td>';
                            echo '<td>' . $blog['reprint'] . '</td>';
                            echo '<td>' . $blog['version'] . '</td>';
                            echo '<td>' . $blog['comment'] . '</td>';
                            echo '</tr>';
                            $visit += intval($blog['visit']);
                            $integration += intval($blog['integration']);
                            $original += intval($blog['original']);
                            $reprint += intval($blog['reprint']);
                            $version += intval($blog['version']);
                            $comment += intval($blog['comment']);
                        }
                        echo '<tr style="font-weight:bold;color:#0B8D9A;"><td colspan="2">Total</td><td>' . $visit . '</td><td>' . $integration . '</td><td>' . $original . '</td><td>' . $reprint . '</td><td>' . $version . '</td><td>' . $comment . '</td></tr>';
                        ?>
                    </tbody>
                </table>
            </div>            
        </div>
        <h2 class="sub-header"><?= $wx_date ?> <span style="color: #c00;">微信群数据</span></h2>
        <div class="row placeholders">
            <!--            <div class="col-xs-6 col-sm-2 placeholder" style="height: 180px; background-color:#ffaf3e;border-radius: 50%;color: #fff;font-weight: bold;font-size: 18px;">
                            <br><br><br>
                            <span><?= $total_article ?></span>
                            <p>总阅读数</p>
                        </div>-->
            <div class="table-responsive col-xs-6 col-sm-9 placeholder">
                <!--<p><em>备注：</em>黄色单元格表示阅读量大于5000.</p>-->
                <table class="table table-bordered  table-hover table-condensed">
                    <thead><tr style='font-weight:bold;'><td>日期</td><td>群名</td><td>管理部门</td><td>成员数</td></tr></thead>
                    <tbody>
                        <?php
                        foreach ($dbh->query($sql_wx_group) as $wx_group) {
                            echo '<tr>';
                            echo '<td>' . $wx_date . '</td>';
                            echo '<td>' . $wx_group['name'] . '</td>';
                            echo '<td>' . $wx_group['manager'] . '</td>';
                            echo '<td>' . $wx_group['num'] . '</td>';
                            echo '</tr>';
                        }
                        ?>
                    </tbody>
                </table>
            </div>            
        </div>        
    </div>
    </div>
    <?php
    $dbh = null;
// 现在运行完成，在此关闭连接
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
include_once 'template/footer.php';
?>